<?php 
/**
 *
 * @category    API
 * @package     Api_Song
 * @subpackage  Model
 * @author      trungpm
 */
class Api_Model_Song {
	const table_name = 'tblvgt_song';
	const table_object = 'tblvgt_object';
	
    /* Check that the name exists and is 300 characters or less long */
    function VerifySongName($SongName, $i, $Errors) {
        if ((strlen($SongName) < 1) or (strlen($SongName) > 300)) {
            $Errors[$i] = IncorrectSongNameLength;
        }
        return $Errors;
    }
    
	/*
     * author: trungpm
     */
    function VerifySongNameExists($SongName, $i, $Errors, $db) {
        $Searchsql = 'SELECT count(song_id) as c FROM tblvgt_song WHERE locked = 0 AND song_name=N\''.$SongName.'\'';
        $SearchResult = DB_query($Searchsql, $db);
        $answer = DB_fetch_array($SearchResult);
        if ($answer['c'] != 0) {
            $Errors[$i] = SongNameAlreadyExists;
        }
        DB_free_result($SearchResult);
        return $Errors;
    }
	
	function checkFieldExisted($table, $field, $value) {
		
	}
	
	
	/* 
     * author: trungpm
     */
    function VerifySongNumberExists($SongNumber, $SongPartnerId, $i, $Errors, $db) {
        $Searchsql = 'SELECT count(song_id) as c FROM tblvgt_song 
        WHERE locked = 0 AND song_number=N\''.$SongNumber.'\' AND partner_id = '. $SongPartnerId;
        $SearchResult = DB_query($Searchsql, $db);
        $answer = DB_fetch_array($SearchResult);
        if ($answer['c'] != 0) {
            $Errors[$i] = SongNumberAlreadyExists;
        }
        DB_free_result($SearchResult);
        return $Errors;
    }
	
	/* 
     * author: trungpm
     */
    function GetSongByNumber($SongNumber, $SongPartnerId, $user, $password) {
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }
		$SongNumber = DB_escape_string($SongNumber);
        $Errors = $this->VerifySongNumberExists($SongNumber, $SongPartnerId, sizeof($Errors), $Errors, $db);
        if (sizeof($Errors) == 0) {
            return $Errors;
        }
        $sql = 'SELECT song.song_id,
					song.song_number,
					song.song_name,
					song.song_unsigned_name,
					song.song_price,
					song.partner_id,
					song.singer_id,
					song.song_singer_sing,
					singer.object_name as singer_name,
					composer.object_name as composer_name,
					song.composer_id,
					song.song_name,
					song.song_singer_authority,
					song.song_composer_authority,
					song.song_release_authority
				FROM tblvgt_song as song
				LEFT JOIN tblvgt_object as singer
				ON song.singer_id = singer.object_id
				LEFT JOIN tblvgt_object as composer
				ON song.composer_id = composer.object_id 				
				WHERE song.locked = 0 AND song.partner_id = '.$SongPartnerId.' AND song_number=N\''. $SongNumber . '\'';
        $result = DB_Query($sql, $db);
        if (sizeof($Errors) != 0) {
            $arrResult = DB_fetch_array($result);
            DB_free_result($result);
            return $arrResult;
        } else {
            return $Errors;
        }
    }
	
	/* 
     * author: trungpm
     */
    function GetSongByName($SongName, $user, $password) {
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }
		$SongName = DB_escape_string($SongName);
        $sql = 'SELECT song_id,
					song_number,
					song_name,
					song_unsigned_name,
					song_price
				FROM tblvgt_song WHERE locked = 0 AND song_name=N\''. $SongName . '\'';
        $result = DB_Query($sql, $db);
		
        $arrResult = DB_fetch_array($result);
        DB_free_result($result);
		return $arrResult;
    }
	
	/* Insert a new Song */
    function InsertSong($SongDetails, $user, $password) {
        $Errors = array();
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }
        foreach ($SongDetails as $key=>$value) {
            $SongDetails[$key] = DB_escape_string($value);
        }
        $Errors = $this->VerifySongNumberExists($SongDetails['song_number'], $SongDetails['partner_id'], sizeof($Errors), $Errors);
        $FieldNames = '';
        $FieldValues = '';
        foreach ($SongDetails as $key=>$value) {
            $FieldNames .= $key.', ';
            $FieldValues .= 'N\''.str_replace("\'", "''", $value).'\', ';
        }
        $sql = 'INSERT INTO tblvgt_song ('.substr($FieldNames, 0, -2).') '.'VALUES ('.substr($FieldValues, 0, -2).') ';
        if (sizeof($Errors) == 0) {
            $result = DB_Query($sql, $db);
            if (!$result) {
                $Errors[0] = DatabaseInsertFailed;
            }
        }
        return $Errors;
    }
	
	function ModifySong($SongId, $SongDetails, $user, $password) {
        $Errors = array();
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }
        foreach ($SongDetails as $key=>$value) {
            $SongDetails[$key] = DB_escape_string($value);
        }
        $sql = 'UPDATE tblvgt_song SET ';
		foreach ($SongDetails as $key => $value) {
			$sql .= $key.'=N\''.str_replace("\'", "''", $value).'\', ';
		}
        $sql = substr($sql, 0, -2).' WHERE song_id='.$SongId;
        
        if (sizeof($Errors) == 0) {
            $result = DB_Query($sql, $db);
            if (!isset($result) || !$result) {
                $Errors[0] = DatabaseUpdateFailed;
            } else {
                $Errors = array();
            }
        }
        return $Errors;
    }
	
    function loadSongs($limit=null, $start=null, &$total, $user, $password, $condition = array()) {
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }
		
		$where = '';
		if(!empty($condition)){
			$arr = array();
			foreach ($condition as $cond) {
				$arr[] = $cond['field'].' '.$cond['relation'].' '.$cond['value'];
			}
			$where = implode(" AND ", $arr);
		}	
				
		if(!empty($where)){
			$where = "WHERE locked = 0 AND $where";        	
		}
		$sql = "SELECT COUNT(song_id) AS size FROM tblvgt_song $where";
        $result = DB_Query($sql, $db);
        $obj = DB_fetch_object($result);
        $total = $obj->size;
		        
		DB_free_result($result);
		
		$sql = "SELECT * FROM(
				SELECT row_number() OVER (ORDER BY s.song_id) AS rownum, 
					s.song_id, 
					s.type_id ,
					s.partner_id,
					s.singer_id,
					oSinger.object_number as singer_number,
					oComposer.object_number as composer_number,				
					s.composer_id,
					s.song_number,
					s.song_name,
					s.song_unsigned_name,
					s.song_price,
					s.song_singer_authority,
					s.song_composer_authority,
					s.song_release_authority,
					oSinger.object_name AS singer_name,
					s.song_singer_sing,
					oComposer.object_name As composer_name
				FROM tblvgt_song AS s
				LEFT JOIN tblvgt_object AS oSinger ON s.singer_id = oSinger.object_id
				LEFT JOIN tblvgt_object AS oComposer ON s.composer_id = oComposer.object_id	
				
				$where) AS A ";
		if(isset($start) && isset($limit)){
			$sql .= "WHERE (A.rownum BETWEEN ($start) AND ($start + $limit)) ";
		}				
        $result = DB_Query($sql, $db);
		
        if (DB_has_rows($result)==0) {
            return null;
        } else {
            return $result;
        }
    }
	
	/* 
	* Insert a new object and return key
	* huudatxm
	*/
    function insert($data, $user, $password) {
        $Errors = array();
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }
                
        $FieldNames = '';
        $FieldValues = '';
        foreach ($data as $key=>$value) {
            $FieldNames .= $key.", ";
            $FieldValues .= 'N\''.$value.'\', ';
        }
		//$FieldNames .= 'created_date, ';
        //$FieldValues .= 'N\''.date('Y-m-d').'\', ';
		$FieldNames .= 'created_by, ';
        $FieldValues .= 'N\''.$_SESSION['UserID'].'\', ';
		$FieldNames .= 'updated_date, ';
        $FieldValues .= 'N\''.date('Y-m-d').'\', ';
		$FieldNames .= 'updated_by, ';
        $FieldValues .= 'N\''.$_SESSION['UserID'].'\', ';
		
        $sql = 'INSERT INTO tblvgt_song ('.substr($FieldNames, 0, -2).') '.' VALUES ('.substr($FieldValues, 0, -2).') ';
                
        if (sizeof($Errors) == 0) {
            DB_Query($sql, $db);
            return $_SESSION['LastInsertId'];	
        }
        return 0;
    }
	
	/* 
	* update 
	* huudatxm
	*/
    function update($data, $user, $password) {
        $Errors = array();
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }
        
		$val_str = $data['field']. ' = \''.$data['value'].'\'';
		$val_str .= ', updated_date = \''.date('Y-m-d').'\' ';        
		$val_str .= ', updated_by = \''.$_SESSION['UserID'].'\' ';
        		
        $sql = 'UPDATE tblvgt_song SET '.$val_str;
		$sql .= ' WHERE song_id = '.$data['song_id'];
		
        if (sizeof($Errors) == 0) {			
            return DB_Query($sql, $db);
        }
        return 0;
    }
	
		/* 
	* delete 
	* huudatxm
	*/
    function delete($song_id, $user, $password) {
        $Errors = array();
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }
        
        $checkId = $this->verifyFieldExist('song_id',$song_id);
		if($checkId == 1) {
			$val_str = 'locked = 1';
			$val_str .= ', updated_date = N\''.date('Y-m-d').'\' ';        
			$val_str .= ', updated_by = N\''.$_SESSION['UserID'].'\' ';	        		
	        $sql = "UPDATE ".self::table_name." SET $val_str WHERE song_id = $song_id";
			
	        if (sizeof($Errors) == 0) {			
	            return DB_Query($sql, $db);
	        }
		}
        return null;        
    }
    
	/**
	 * load datas with paginates
	 *
	 */
	function getSongPaginates($limit, $start, &$total, $conds) {
		//Check user permission
		$db = db($_SESSION['UserID'],  $_SESSION['Password']);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        };		
        //Get search conditions 
		$condsStr = null;
		if(!empty($conds)){
			$arr = array();
			foreach ($conds as $cond) {
				$arr[] = 's.'.$cond['field'].' '.$cond['relation'].' '.$cond['value'];
			}
			$condsStr .= implode(" AND ", $arr);
		};		
		
		$where = '';
		if(!empty($condsStr)) {				
			$where = 'WHERE '.$condsStr;				
		};
		//Get query string for size
		$sql = "SELECT COUNT(*) AS size 
		FROM ".self::table_name." AS s $where";		
		//Get size 
        $result = DB_Query($sql, $db);
        $obj = DB_fetch_object($result);
        $total = $obj->size;		        
		DB_free_result($result);		
		//Get query string with row_num
		$query = "SELECT row_number() OVER (ORDER BY s.song_id) AS rownum, 
					s.song_id, 
					s.type_id ,
					s.partner_id,
					s.singer_id,
					oSinger.object_number as singer_number,
					oComposer.object_number as composer_number,				
					s.composer_id,
					s.song_number,
					s.song_name,
					s.song_unsigned_name,
					s.song_price,
					s.song_singer_authority,
					s.song_composer_authority,
					s.song_release_authority,
					convert(varchar, s.created_date, 101) as created_date,
					convert(varchar, s.ended_date, 101) as ended_date,
					oSinger.object_name AS singer_name,
					s.song_singer_sing,
					oComposer.object_name As composer_name		
				FROM ".self::table_name." AS s
				LEFT JOIN ".self::table_object." AS oSinger ON s.singer_id = oSinger.object_id
				LEFT JOIN ".self::table_object." AS oComposer ON s.composer_id = oComposer.object_id			
				$where";
				
		//Get query string with paginate
		$query = "SELECT * FROM(".$query.") AS A ";
		if(isset($start) && isset($limit)){
			$query .= "WHERE (A.rownum BETWEEN ($start) AND ($start + $limit))";
		}
	
		//Execute query
        $result = DB_Query($query, $db);
		//Return result
        if (DB_has_rows($result)==0) {
            return null;
        } else {
            return $result;
        }
	}
	
	/* Verify field existed.	 
     */
    function verifyFieldExist($field, $value) {
    	//Check user permission
		$db = db($_SESSION['UserID'],  $_SESSION['Password']);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        };
        $Searchsql = "SELECT count(*) as c FROM ".self::table_name." WHERE $field = N'$value'";
        $SearchResult = DB_query($Searchsql, $db);
        $answer = DB_fetch_array($SearchResult);
        if ($answer['c'] != 0) {
            return 1;
        };
        return 0;
    }
    
	/* 
	* Kiem tra bai hat doc quyen co con trong tgian doc quyen neu het thi chuyen ve trang thai khong doc quyen 
	* huudatxm
	*/
    function unExecuteSong($ended_date, $user, $password) {
        $Errors = array();
        $db = db($user, $password);
        if (gettype($db) == 'integer') {
            $Errors[0] = NoAuthorisation;
            return $Errors;
        }
        
		$val_str .= ', updated_date = N\''.date('Y-m-d').'\' ';        
		$val_str .= ', updated_by = N\''.$user.'\' ';	        		
	    $sql = "UPDATE ".self::table_name." SET song_release_authority = 0 
	    WHERE locked = 0 AND ended_date <  N'$ended_date'";

	    if (sizeof($Errors) == 0) {
	        return DB_Query($sql, $db);
	    }
		
        return null;        
    }
}